this report presents the business case for the formulation of a Customer Segmentation strategy by utilizing the information available in a CRM system.
The data we are analysing refer to a multinational IT company which sells 62 unique products across 4 Product Categories in 17 countries. Key Account managers list opportunities in the company’s CRM system each opportunity takes an unique opportunity ID.
lets have a look in our crm data
#importing the data in a dataframe from csv file
crm<-read.csv("crm.csv", header = TRUE,sep = ";")
describe(crm)
## crm
##
## 18 Variables 5766 Observations
## --------------------------------------------------------------------------------
## Billing.Country
## n missing distinct
## 5766 0 17
##
## lowest : Albania Bosnia and Herzegovina Bulgaria Croatia Cyprus
## highest: Serbia Slovakia Slovenia Turkey Ukraine
##
## Albania (235, 0.041), Bosnia and Herzegovina (257, 0.045), Bulgaria (1604,
## 0.278), Croatia (578, 0.100), Cyprus (65, 0.011), Czech Republic (109, 0.019),
## FYROM (158, 0.027), Greece (541, 0.094), Kosovo (149, 0.026), Moldova, Republic
## of (9, 0.002), Montenegro (48, 0.008), Romania (517, 0.090), Serbia (222,
## 0.039), Slovakia (215, 0.037), Slovenia (794, 0.138), Turkey (43, 0.007),
## Ukraine (222, 0.039)
## --------------------------------------------------------------------------------
## Opportunity.ID
## n missing distinct
## 5766 0 2042
##
## lowest : OPP-000999 OPP-001000 OPP-001078 OPP-001192 OPP-001193
## highest: OPP-011751 OPP-011753 OPP-011757 OPP-011804 OPP-012009
## --------------------------------------------------------------------------------
## Account.Name
## n missing distinct Info Mean Gmd .05 .10
## 5766 0 2042 1 8623 1929 2850 7383
## .25 .50 .75 .90 .95
## 8183 8780 9819 10632 10916
##
## lowest : 999 1000 1078 1192 1193, highest: 11751 11753 11757 11804 12009
## --------------------------------------------------------------------------------
## Parent.Account
## n missing distinct
## 5766 0 15
##
## lowest : Parent_1 Parent_10 Parent_11 Parent_12
## highest: Parent_5 Parent_6 Parent_7 Parent_8 Parent_9
##
## (4157, 0.721), Parent_1 (49, 0.008), Parent_10 (61, 0.011), Parent_11 (6,
## 0.001), Parent_12 (62, 0.011), Parent_13 (8, 0.001), Parent_14 (2, 0.000),
## Parent_2 (457, 0.079), Parent_3 (172, 0.030), Parent_4 (75, 0.013), Parent_5
## (293, 0.051), Parent_6 (175, 0.030), Parent_7 (65, 0.011), Parent_8 (130,
## 0.023), Parent_9 (54, 0.009)
## --------------------------------------------------------------------------------
## Type
## n missing distinct
## 5766 0 2
##
## Value Existing Business New Business
## Frequency 4867 899
## Proportion 0.844 0.156
## --------------------------------------------------------------------------------
## Invoice.Date
## n missing distinct
## 5766 0 283
##
## lowest : 1/13/2017 1/14/2017 1/16/2017 1/17/2017 1/19/2017
## highest: 9/26/2017 9/27/2017 9/29/2017 9/30/2016 9/30/2017
## --------------------------------------------------------------------------------
## Schedule.Date
## n missing distinct
## 5766 0 321
##
## lowest : 1/1/17 1/10/17 1/11/17 1/12/17 1/13/2017
## highest: 9/3/17 9/30/2017 9/5/17 9/6/17 9/8/17
## --------------------------------------------------------------------------------
## Amount..converted..Currency
## n missing distinct value
## 5766 0 1 EUR
##
## Value EUR
## Frequency 5766
## Proportion 1
## --------------------------------------------------------------------------------
## Amount..converted.
## n missing distinct Info Mean Gmd .05 .10
## 5766 0 1774 1 113208 178297 370.3 763.1
## .25 .50 .75 .90 .95
## 3717.8 19584.0 82612.9 335143.4 653012.1
##
## lowest : 0.27 15.34 15.73 26.60 29.00
## highest: 1830000.00 2052300.00 2075910.32 2090000.00 2104000.00
## --------------------------------------------------------------------------------
## Quantity
## n missing distinct Info Mean Gmd .05 .10
## 5766 0 349 0.974 2474 4723 1 1
## .25 .50 .75 .90 .95
## 1 12 242 1858 7047
##
## lowest : 1.0 2.0 3.0 3.5 4.0
## highest: 52800.0 62500.0 74259.0 149829.0 552715.0
##
## Value 0 5000 10000 15000 20000 30000 35000 40000 45000
## Frequency 5247 237 119 40 53 13 1 5 24
## Proportion 0.910 0.041 0.021 0.007 0.009 0.002 0.000 0.001 0.004
##
## Value 55000 60000 75000 150000 555000
## Frequency 1 1 12 1 12
## Proportion 0.000 0.000 0.002 0.000 0.002
##
## For the frequency table, variable is rounded to the nearest 5000
## --------------------------------------------------------------------------------
## Schedule.Amount..converted..Currency
## n missing distinct value
## 5766 0 1 EUR
##
## Value EUR
## Frequency 5766
## Proportion 1
## --------------------------------------------------------------------------------
## Schedule.Amount..converted.
## n missing distinct Info Mean Gmd .05 .10
## 5766 0 3109 1 30864 54336 51.13 145.02
## .25 .50 .75 .90 .95
## 600.00 2375.00 11063.05 46558.75 125000.00
##
## lowest : -11394.63 -3436.90 -1986.04 -1315.20 -986.40
## highest: 1038826.44 1122000.00 1162431.00 1304000.00 1572100.00
## --------------------------------------------------------------------------------
## Gross.Margin..
## n missing distinct Info Mean Gmd .05 .10
## 5766 0 542 0.996 45.05 33.5 3.907 6.895
## .25 .50 .75 .90 .95
## 20.000 43.730 60.000 100.000 100.000
##
## lowest : 0.01 0.10 0.97 1.00 1.38, highest: 119.00 179.00 308.00 630.00 900.00
##
## Value 0 10 20 30 40 50 60 70 80 90 100
## Frequency 475 569 782 493 646 920 704 203 249 55 665
## Proportion 0.082 0.099 0.136 0.086 0.112 0.160 0.122 0.035 0.043 0.010 0.115
##
## Value 120 180 310 630 900
## Frequency 1 1 1 1 1
## Proportion 0.000 0.000 0.000 0.000 0.000
##
## For the frequency table, variable is rounded to the nearest 10
## --------------------------------------------------------------------------------
## Stage
## n missing distinct
## 5766 0 2
##
## Value Closed Won Invoiced
## Frequency 1878 3888
## Proportion 0.326 0.674
## --------------------------------------------------------------------------------
## Probability....
## n missing distinct Info Mean Gmd
## 5766 0 2 0.659 96.74 4.393
##
## Value 90 100
## Frequency 1878 3888
## Proportion 0.326 0.674
## --------------------------------------------------------------------------------
## Forecast.Category
## n missing distinct
## 5766 0 2
##
## Value Closed Commit
## Frequency 3888 1878
## Proportion 0.674 0.326
## --------------------------------------------------------------------------------
## Product.Name
## n missing distinct
## 5766 0 62
##
## lowest : Product_1 Product_10 Product_11 Product_12 Product_13
## highest: Product_61 Product_62 Product_7 Product_8 Product_9
## --------------------------------------------------------------------------------
## Product_.Category
## n missing distinct
## 5766 0 4
##
## Value Hardware Outsourcing Services Software
## Frequency 1662 241 2961 902
## Proportion 0.288 0.042 0.514 0.156
## --------------------------------------------------------------------------------
as we can see our data constist of 18 variables and 5766 observations and there are no missing observations for the our variables.
Above we can find the relative frequency and the proportion were applicable for each variable and drow the following conclusions:
Since our data concists of 5766 observations we take a random sample of 28 entries to see a preview of the data we are working on
#taking a random sample of our data 0,5%
(formattable(sample_frac(crm,0.005)))
| Billing.Country | Opportunity.ID | Account.Name | Parent.Account | Type | Invoice.Date | Schedule.Date | Amount..converted..Currency | Amount..converted. | Quantity | Schedule.Amount..converted..Currency | Schedule.Amount..converted. | Gross.Margin.. | Stage | Probability…. | Forecast.Category | Product.Name | Product_.Category |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Bulgaria | OPP-008773 | 8773 | New Business | 43009 | 10/6/17 | EUR | 74518.01 | 828 | EUR | 6209.83 | 50.00 | Invoiced | 100 | Closed | Product_5 | Services | |
| Ukraine | OPP-002853 | 2853 | Existing Business | 42705 | 1/3/17 | EUR | 122113.07 | 4933 | EUR | 3545.02 | 25.00 | Closed Won | 90 | Commit | Product_5 | Services | |
| Bulgaria | OPP-008773 | 8773 | New Business | 43009 | 10/1/17 | EUR | 74518.01 | 828 | EUR | 6209.83 | 50.00 | Invoiced | 100 | Closed | Product_5 | Services | |
| Romania | OPP-003651 | 3651 | New Business | 12/30/2016 | 7/25/2017 | EUR | 60216.00 | 12 | EUR | 5018.00 | 45.00 | Invoiced | 100 | Closed | Product_33 | Services | |
| FYROM | OPP-008517 | 8517 | Existing Business | 42736 | 1/8/17 | EUR | 15166.20 | 9420 | EUR | 1263.85 | 100.00 | Closed Won | 90 | Commit | Product_6 | Services | |
| Bulgaria | OPP-009955 | 9955 | Parent_3 | Existing Business | 5/30/2017 | 5/30/2017 | EUR | 100.00 | 2 | EUR | 100.00 | 25.00 | Invoiced | 100 | Closed | Product_26 | Hardware |
| Romania | OPP-008069 | 8069 | Parent_8 | Existing Business | 43076 | 9/30/2017 | EUR | 15132.00 | 16 | EUR | 3783.00 | 5.56 | Invoiced | 100 | Closed | Product_27 | Software |
| Slovenia | OPP-009347 | 9347 | Existing Business | 8/24/2017 | 9/30/2017 | EUR | 15300.00 | 1 | EUR | 900.00 | 66.67 | Invoiced | 100 | Closed | Product_31 | Services | |
| Bulgaria | OPP-009701 | 9701 | Existing Business | 42829 | 4/4/17 | EUR | 3913.33 | 1 | EUR | 3913.33 | 20.00 | Invoiced | 100 | Closed | Product_14 | Software | |
| Slovenia | OPP-009155 | 9155 | New Business | 42737 | 1/2/17 | EUR | 1080.00 | 6 | EUR | 1080.00 | 80.00 | Closed Won | 90 | Commit | Product_39 | Services | |
| Ukraine | OPP-002862 | 2862 | Existing Business | 42705 | 1/3/17 | EUR | 6509.51 | 397 | EUR | 224.21 | 30.00 | Closed Won | 90 | Commit | Product_5 | Services | |
| Slovenia | OPP-006988 | 6988 | Existing Business | 42924 | 7/8/17 | EUR | 19584.00 | 24 | EUR | 816.00 | 89.00 | Closed Won | 90 | Commit | Product_39 | Services | |
| Czech Republic | OPP-008922 | 8922 | Existing Business | 4/30/2017 | 4/30/2017 | EUR | 2347.97 | 1 | EUR | 2347.97 | 100.00 | Invoiced | 100 | Closed | Product_6 | Services | |
| Slovenia | OPP-006189 | 6189 | Existing Business | 6/30/2017 | 11/30/2017 | EUR | 9949.85 | 6 | EUR | 2312.47 | 15.00 | Closed Won | 90 | Commit | Product_6 | Services | |
| Bosnia and Herzegovina | OPP-008430 | 8430 | Existing Business | 1/31/2017 | 5/25/2017 | EUR | 22574.76 | 18529 | EUR | 3869.90 | 56.00 | Closed Won | 90 | Commit | Product_5 | Services | |
| Romania | OPP-008021 | 8021 | Existing Business | 3/31/2017 | 3/31/2017 | EUR | 15200.00 | 1 | EUR | 3800.00 | 10.00 | Closed Won | 90 | Commit | Product_27 | Software | |
| Bulgaria | OPP-008765 | 8765 | Parent_5 | Existing Business | 42826 | 4/7/17 | EUR | 364360.87 | 6152 | EUR | 31021.60 | 50.00 | Invoiced | 100 | Closed | Product_5 | Services |
| Slovenia | OPP-008214 | 8214 | Parent_5 | Existing Business | 1/28/2017 | 6/28/2017 | EUR | 48429.00 | 172 | EUR | 1755.00 | 64.94 | Invoiced | 100 | Closed | Product_5 | Services |
| Croatia | OPP-002753 | 2753 | New Business | 12/31/2020 | 5/31/2017 | EUR | 100535.00 | 24 | EUR | 2861.00 | 10.00 | Closed Won | 90 | Commit | Product_1 | Outsourcing | |
| Bulgaria | OPP-011669 | 11669 | Existing Business | 12/29/2017 | 12/29/2017 | EUR | 1777.48 | 1 | EUR | 1777.48 | 20.00 | Invoiced | 100 | Closed | Product_26 | Hardware | |
| Romania | OPP-009114 | 9114 | Parent_7 | New Business | 3/23/2017 | 3/23/2017 | EUR | 41507.50 | 170 | EUR | 2380.00 | 37.00 | Invoiced | 100 | Closed | Product_7 | Software |
| Bosnia and Herzegovina | OPP-009372 | 9372 | Existing Business | 2/28/2017 | 2/28/2017 | EUR | 488.80 | 1 | EUR | 488.80 | 100.00 | Invoiced | 100 | Closed | Product_5 | Services | |
| Slovenia | OPP-010013 | 10013 | Existing Business | 10/31/2017 | 10/31/2017 | EUR | 63033.00 | 1 | EUR | 1836.00 | 78.00 | Invoiced | 100 | Closed | Product_31 | Services | |
| Albania | OPP-009784 | 9784 | Parent_1 | Existing Business | 6/30/2017 | 1/4/17 | EUR | 18964.50 | 991 | EUR | 888.30 | 100.00 | Invoiced | 100 | Closed | Product_1 | Outsourcing |
| Slovenia | OPP-008213 | 8213 | Existing Business | 1/28/2017 | 1/28/2017 | EUR | 45528.00 | 480 | EUR | 0.00 | 71.78 | Invoiced | 100 | Closed | Product_5 | Services | |
| Serbia | OPP-008691 | 8691 | Parent_6 | New Business | 1/31/2017 | 2/28/2017 | EUR | 100620.00 | 129 | EUR | 8385.00 | 40.00 | Invoiced | 100 | Closed | Product_5 | Services |
| Bulgaria | OPP-004870 | 4870 | Existing Business | 2/22/2016 | 1/1/17 | EUR | 587412.96 | 5896 | EUR | 0.00 | 50.00 | Invoiced | 100 | Closed | Product_5 | Services | |
| Croatia | OPP-001000 | 1000 | New Business | 12/31/2020 | 4/30/2017 | EUR | 32388.30 | 231 | EUR | 1270.74 | 100.00 | Closed Won | 90 | Commit | Product_6 | Services | |
| Turkey | OPP-009181 | 9181 | New Business | 42377 | 1/1/17 | EUR | 78277.89 | 1 | EUR | 19569.47 | 30.80 | Invoiced | 100 | Closed | Product_17 | Software |
Billing.Country: The country in which the customer operates.Opportunity.ID : Unique system ID for each opportunity listed in the CRM.Account.Name : Customer Name.Parent.Account: Some customers operateType : if the customer is a returning customer (“Existing”) or a new customer (“New Business”)Invoice.Date: the date that we invoiced the customer.Schedule.Date: In some cases we invoice based on a schedule especially if the opportunity has i) many products that have different deliveries dates attached to it. ii) we have an outsourcing or a service contract in place where we invoice the customer monthly so in that case the Invoice.Date could by 30/12/2017 and we will have 12 entries with different Schedule.Date.Amount..converted..Currency: Denotes the currency of the Amount..converted..Amount..converted. : Most CRM systems convert the local country currency to a predifined one for management reporting purposes in our case the selected currency is EUR.Quantity : Product quantity.Schedule.Amount..converted..Currency: Denotes the currency of the Schedule.Amount..converted.Schedule.Amount..converted.: When we invoice based on a schedule the total Amount..converted. of the opportunity is broken down based on the schedule.Gross.Margin..: the Gross Margin of each opportunity.Stage : the stage of the opportunity i.e invoiced, Closed WonProbability.... : the probability of completion for the opportunity.Forecast.Category: Characterizes if an opportunity is Closed or Committed mainly used for pipeline monitoring purposes.Product.Name: The name of the product.Product.Category : Category of the product i.e Hardware, Services , Outsourcing, SoftwareRecency= When we last invoiced the customer? (counted in days)
Frequency= How often do we invoice the customer?
Monetary value= What is the monetary value the customer? i.e Revenue/Frequency
Once we have R,F,M values from the purchase history,we assign a score from one (1) to five (5) to Recency, Frequency and Monetary values individually for each customer.
Five (5) is the best/highest value, and one (1) is the lowest/worst value.
A final RFM score is calculated simply by combining individual R,F,M score numbers. then we can segment our customers based on the rules in the following table
| Recency Score | Combined FM Score | Segment |
|---|---|---|
| 5 | 5 | Champions |
| 4-5 | 4-5 | Loyal |
| 4-5 | 0-3 | Promising |
| 2-3 | 2-3 | Needs Attention |
| 2-4 | 1-4 | Slipping |
| 1-3 | 0-5 | At Risk |
We will use for our analyis on the Schedule.Amount..converted. and the Schedule.Date fields for the following reason since there are opportunities which are depicted in the system with multiple lines for the same Opportunity.ID those two fields correspond to the true sale revenue and the true invoice date. Also we will group our crm dataset by Billing.Country,Account.Name and Parent.Account cause we must define the whole purchasing history we have with each customer and each customer might have various opportunities listed to them.
we choose as a report date 31/12/2017 Now we need to calculate some new variables
Revenue: Invoiced Revenue corresponding to each customer.In order to calculate revenue we will sum up all the Schedule.Amount..converted. per Account.Name.
GrossProffit:Gross Proffit per customer.In order to calculate Gross Profit we will used the following formula Revenue x Gross.Margin...
Recency: When did we last invoiced the customer, counted in days. In order to calculate recency we will take the difference in days from our report date (i.e.31/12/2017) from the maximum Schedule.Date for each customer. This ensures that if a customer has more than one opportunities listed the most recent invoice date will be used for the calculation.
Frequency: How often do we invoice the customer. In order to calculate frequency will count the distict Schedule.Date for each customer.So if we have issued multiple invoices related with multiple opportunities for the same customer in the same date this will be counted as a one time invoicing.#Setting a report date
rdate2017=as.Date("2017-12-31")
# CRM data wrangling 1 :date transformations and filtering and saving the results in a new dataframe named seg2017
seg2017<-crm%>%mutate(`Schedule.Date`=mdy(`Schedule.Date`))%>%filter(Schedule.Date > "2016-01-31" & Schedule.Date <"2018-01-01")
#%>%mutate(`Invoice.Date`=mdy(`Invoice.Date`))
#CRM data wrangling 2:calculating Gross Profit in the seg2017 dataframe
seg2017<-seg2017%>%mutate(`Gross.Profit`=(Schedule.Amount..converted.*Gross.Margin..)/100)
# grouping seg2017 calculating Recency/Frequncy/Monetary/Profit and Gross Profit 2017 and saving the results in a new datarame named RFM_seg2017
RFM_seg2017<-seg2017%>%group_by(Billing.Country,Account.Name,Parent.Account)%>%
summarise(Recency=as.numeric(rdate2017-max(Schedule.Date)),Frequency=n_distinct(Schedule.Date),Monetary= sum(as.numeric(Schedule.Amount..converted.),na.rm =TRUE)/n_distinct(Schedule.Date),Revenue=sum(as.numeric(Schedule.Amount..converted.),na.rm=TRUE), `Gross.Profit`=sum(Gross.Profit,na.rm=TRUE))
# Order the RFM_seg2017 dataframe by Revenue
RFM_seg2017<-RFM_seg2017[order(-RFM_seg2017$Revenue),]
#present a few lines of RFM_seg2017
formattable(head(RFM_seg2017,15))
| Billing.Country | Account.Name | Parent.Account | Recency | Frequency | Monetary | Revenue | Gross.Profit |
|---|---|---|---|---|---|---|---|
| Croatia | 8575 | 25 | 14 | 4097515.7 | 57365220 | 5092530.8 | |
| Croatia | 8576 | 52 | 14 | 690870.1 | 9672182 | 974449.4 | |
| Romania | 8036 | Parent_2 | 0 | 3 | 1932092.4 | 5796277 | 1961179.5 |
| Romania | 8741 | 184 | 2 | 1956000.0 | 3912000 | 1312923.2 | |
| Greece | 8132 | 0 | 7 | 498184.7 | 3487293 | 1679033.5 | |
| Greece | 8118 | 87 | 5 | 532463.2 | 2662316 | 477234.1 | |
| Greece | 10345 | 184 | 2 | 1052000.0 | 2104000 | 504333.2 | |
| Greece | 8989 | 184 | 2 | 1026150.0 | 2052300 | 354498.3 | |
| Croatia | 8751 | 180 | 4 | 511549.1 | 2046196 | 133875.9 | |
| Greece | 10333 | 184 | 1 | 1830000.0 | 1830000 | 363823.2 | |
| Romania | 7051 | 276 | 1 | 1734700.0 | 1734700 | 135306.6 | |
| Romania | 8497 | Parent_5 | 247 | 3 | 564213.3 | 1692640 | 223223.9 |
| Greece | 8129 | 353 | 9 | 177435.4 | 1596919 | 175661.1 | |
| Greece | 10348 | 92 | 1 | 1551000.0 | 1551000 | 301248.9 | |
| Croatia | 8864 | Parent_8 | 92 | 5 | 293155.0 | 1465775 | 343090.4 |
#Calucalating R/F/M Quantiles 2017: taking the 3 columns (Recency, Frequency,Monetary) and scalling them
rfm_quant_2017 <- RFM_seg2017[c(4:6)]%>%mutate(R=ntile(desc(Recency),5),F=ntile(Frequency,5),M=ntile(Monetary,5))%>%mutate(RFM= paste0(R,F,M))%>%mutate(RFM=as.numeric(RFM))%>%mutate(FM=ntile(F+M,5))
#creating an id column in order to join the 2 dataframes
RFM_seg2017$id= 1:nrow(RFM_seg2017)
rfm_quant_2017$id=1:nrow(rfm_quant_2017)
RFM_seg2017<-full_join(RFM_seg2017,rfm_quant_2017)
## Joining, by = c("Recency", "Frequency", "Monetary", "id")
#dropping the id columnn
RFM_seg2017 <- RFM_seg2017[-c(9)]
#characterise customers based on their RFM score
RFM_seg2017<-RFM_seg2017%>%mutate(Segment=if_else(R%in% c(5)&FM%in%c(5),"Champions",if_else(R%in% c(4:5)&FM%in%c(4:5),"Loyal",if_else(R%in% c(4:5)&FM%in%c(0:3),"Promising",if_else(R%in% c(2:3)&FM%in%c(2:3),"Need Attention",if_else(R%in% c(2:4)&FM%in%c(1:4),"Slipping",if_else(R%in% c(1:3)&FM%in%c(0:5),"At Risk",false=NULL)))))))
formattable(head(RFM_seg2017,15))
| Billing.Country | Account.Name | Parent.Account | Recency | Frequency | Monetary | Revenue | Gross.Profit | R | F | M | RFM | FM | Segment |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Croatia | 8575 | 25 | 14 | 4097515.7 | 57365220 | 5092530.8 | 5 | 5 | 5 | 555 | 5 | Champions | |
| Croatia | 8576 | 52 | 14 | 690870.1 | 9672182 | 974449.4 | 4 | 5 | 5 | 455 | 5 | Loyal | |
| Romania | 8036 | Parent_2 | 0 | 3 | 1932092.4 | 5796277 | 1961179.5 | 5 | 5 | 5 | 555 | 5 | Champions |
| Romania | 8741 | 184 | 2 | 1956000.0 | 3912000 | 1312923.2 | 2 | 4 | 5 | 245 | 5 | At Risk | |
| Greece | 8132 | 0 | 7 | 498184.7 | 3487293 | 1679033.5 | 5 | 5 | 5 | 555 | 5 | Champions | |
| Greece | 8118 | 87 | 5 | 532463.2 | 2662316 | 477234.1 | 4 | 5 | 5 | 455 | 5 | Loyal | |
| Greece | 10345 | 184 | 2 | 1052000.0 | 2104000 | 504333.2 | 2 | 4 | 5 | 245 | 5 | At Risk | |
| Greece | 8989 | 184 | 2 | 1026150.0 | 2052300 | 354498.3 | 2 | 4 | 5 | 245 | 5 | At Risk | |
| Croatia | 8751 | 180 | 4 | 511549.1 | 2046196 | 133875.9 | 3 | 5 | 5 | 355 | 5 | At Risk | |
| Greece | 10333 | 184 | 1 | 1830000.0 | 1830000 | 363823.2 | 2 | 1 | 5 | 215 | 3 | Need Attention | |
| Romania | 7051 | 276 | 1 | 1734700.0 | 1734700 | 135306.6 | 1 | 1 | 5 | 115 | 3 | At Risk | |
| Romania | 8497 | Parent_5 | 247 | 3 | 564213.3 | 1692640 | 223223.9 | 2 | 5 | 5 | 255 | 5 | At Risk |
| Greece | 8129 | 353 | 9 | 177435.4 | 1596919 | 175661.1 | 1 | 5 | 5 | 155 | 5 | At Risk | |
| Greece | 10348 | 92 | 1 | 1551000.0 | 1551000 | 301248.9 | 4 | 1 | 5 | 415 | 3 | Promising | |
| Croatia | 8864 | Parent_8 | 92 | 5 | 293155.0 | 1465775 | 343090.4 | 4 | 5 | 5 | 455 | 5 | Loyal |
#Ordering vector
myorder<-c('Champions','Loyal','Promising','Need Attention','Slipping','At Risk')
#Grouping RFM_seg2017 by Segment and creating a new df named revseg2017
revseg2017<-RFM_seg2017%>%group_by(Segment)%>%summarise(Revenue=sum(as.numeric(Revenue),na.rm=TRUE),No.Customers=sum(n_distinct(Account.Name)))%>%arrange(factor(Segment, levels =myorder))
revseg2017$Revenue<-currency(revseg2017$Revenue,symbol = "€",digits=0)
#showing revseg2017
formattable(revseg2017)
| Segment | Revenue | No.Customers |
|---|---|---|
| Champions | €88,535,821 | 170 |
| Loyal | €22,091,431 | 253 |
| Promising | €15,029,625 | 393 |
| Need Attention | €14,214,616 | 374 |
| Slipping | €2,225,945 | 340 |
| At Risk | €35,861,630 | 512 |
library(treemapify)
library(ggfittext)
library(viridis)
vectorc=c("red","orange","blue","green")
tree<-ggplot(revseg2017, aes(area =Revenue, fill =Revenue, label = paste(No.Customers,Segment,format(Revenue/1000000 ,digits=0,nsmall=2, big.mark=","),"M€")))+geom_treemap() +geom_treemap_text(fontface = "italic", colour = "white", place = "centre",grow = T,reflow=T,size=60)+theme(legend.position = "right")+labs(title =paste("Revenue by Customer Segment\n Total Revenue=",format(sum(revseg2017$Revenue),big.mark = ","),"€"),caption = "The area of each tile represents the proportion of Revenue")+scale_fill_continuous(limits=c(000000, 90000000), breaks=seq(0000000, 90000000,by=15000000),low="orangered1",high="darkblue",label = dollar_format(suffix = "€", prefix = ""))
tree
library(plotly)
pie <-revseg2017%>% plot_ly( labels = ~Segment, values = ~Revenue )%>%add_pie(hole = 0.6) %>%layout(title = 'Revenue by Segment',xaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE),
yaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE))
#type = 'pie'after values in order to get a pie chart)>
pie%>%layout(showlegend = TRUE)%>% config(displayModeBar = F)
p <-revseg2017%>% plot_ly( y = ~Segment, x = ~Revenue, type = 'bar',orientation = 'h',height=450,width=750)%>%layout(margin =list(l = 110, r = 100, b = 50, t =50, pad = 4))
p%>%layout%>% config(displayModeBar = F)
newdf<-merge(seg2017, RFM_seg2017[, c("Account.Name", "Segment")], by="Account.Name")
revprod<-newdf%>%group_by(Product.Name,Segment)%>% summarise(Revenue=sum(Schedule.Amount..converted.,na.rm=TRUE),No.Customers=sum(n_distinct(Account.Name)))
a<-revprod%>%group_by(Product.Name)%>%summarise(Revenue=sum(Revenue,na.rm=TRUE),No.Customers=sum(No.Customers,na.rm=TRUE))
a$Total="Total"
#unite(a, Product.Name, Total, sep=" ")
c<-bind_rows(revprod,a)
c[order(c$Product.Name),]
## # A tibble: 317 x 5
## # Groups: Product.Name [62]
## Product.Name Segment Revenue No.Customers Total
## <fct> <chr> <dbl> <int> <chr>
## 1 Product_1 At Risk 138293. 4 <NA>
## 2 Product_1 Champions 1452807. 10 <NA>
## 3 Product_1 Loyal 37801. 2 <NA>
## 4 Product_1 <NA> 1628901. 16 Total
## 5 Product_10 Loyal 27435. 6 <NA>
## 6 Product_10 Promising 36649. 4 <NA>
## 7 Product_10 Slipping 36571. 7 <NA>
## 8 Product_10 <NA> 100655. 17 Total
## 9 Product_11 At Risk 168219. 5 <NA>
## 10 Product_11 Champions 48758. 1 <NA>
## # … with 307 more rows
d<-c%>%unite(Product.Name, Product.Name, Total, sep=" ")
d$Product.Name<- gsub("NA"," ",d$Product.Name)
d$Segment<- gsub("NAN","a",d$Segment)
d<-d[order(c$Product.Name,c$Revenue),]
d<-d%>%mutate(Segment=replace(Segment,is.na(Segment),""))%>%mutate(No.Customers=replace(No.Customers,is.na(No.Customers),""))
d$Revenue<-currency(d$Revenue,symbol = "€",digits=0)
formattable(d)
| Product.Name | Segment | Revenue | No.Customers |
|---|---|---|---|
| Product_1 | Loyal | €37,801 | 2 |
| Product_1 | At Risk | €138,293 | 4 |
| Product_1 | Champions | €1,452,807 | 10 |
| Product_1 Total | €1,628,901 | 16 | |
| Product_10 | Loyal | €27,435 | 6 |
| Product_10 | Slipping | €36,571 | 7 |
| Product_10 | Promising | €36,649 | 4 |
| Product_10 Total | €100,655 | 17 | |
| Product_11 | Need Attention | €6,000 | 1 |
| Product_11 | Promising | €8,375 | 4 |
| Product_11 | Slipping | €20,725 | 3 |
| Product_11 | Loyal | €31,170 | 3 |
| Product_11 | Champions | €48,758 | 1 |
| Product_11 | At Risk | €168,219 | 5 |
| Product_11 Total | €283,248 | 17 | |
| Product_12 | Promising | €40,860 | 4 |
| Product_12 | Need Attention | €56,590 | 4 |
| Product_12 | Slipping | €90,014 | 9 |
| Product_12 | Loyal | €173,990 | 2 |
| Product_12 | At Risk | €226,703 | 10 |
| Product_12 | Champions | €476,311 | 14 |
| Product_12 Total | €1,064,468 | 43 | |
| Product_13 | At Risk | €30,160 | 3 |
| Product_13 | Slipping | €34,416 | 4 |
| Product_13 | Loyal | €48,156 | 8 |
| Product_13 | Promising | €127,023 | 6 |
| Product_13 | Champions | €1,263,361 | 8 |
| Product_13 Total | €1,503,116 | 29 | |
| Product_14 | Slipping | €19,299 | 5 |
| Product_14 | Need Attention | €81,956 | 2 |
| Product_14 | At Risk | €204,539 | 6 |
| Product_14 | Promising | €326,367 | 13 |
| Product_14 | Loyal | €2,424,181 | 10 |
| Product_14 | Champions | €11,361,472 | 2 |
| Product_14 Total | €14,417,815 | 38 | |
| Product_15 | Loyal | €5,798 | 1 |
| Product_15 | Champions | €18,730 | 1 |
| Product_15 | Promising | €39,900 | 1 |
| Product_15 | At Risk | €73,000 | 2 |
| Product_15 | Need Attention | €105,000 | 1 |
| Product_15 Total | €242,428 | 6 | |
| Product_16 | Champions | €2,600 | 1 |
| Product_16 | Need Attention | €5,062 | 8 |
| Product_16 | Slipping | €62,500 | 8 |
| Product_16 | Loyal | €120,709 | 12 |
| Product_16 | At Risk | €198,639 | 4 |
| Product_16 | Promising | €263,161 | 23 |
| Product_16 Total | €652,671 | 56 | |
| Product_17 | Need Attention | €450 | 1 |
| Product_17 | Loyal | €24,592 | 4 |
| Product_17 | Slipping | €74,946 | 8 |
| Product_17 | Promising | €181,310 | 7 |
| Product_17 | Champions | €220,350 | 5 |
| Product_17 | At Risk | €718,756 | 25 |
| Product_17 Total | €1,220,404 | 50 | |
| Product_18 | Slipping | €19,900 | 1 |
| Product_18 | Loyal | €123,009 | 2 |
| Product_18 | Promising | €149,342 | 6 |
| Product_18 Total | €292,251 | 9 | |
| Product_19 | Promising | €61,660 | 2 |
| Product_19 Total | €61,660 | 2 | |
| Product_2 | Promising | €4,950 | 1 |
| Product_2 | Loyal | €10,088 | 1 |
| Product_2 | Champions | €236,411 | 2 |
| Product_2 | At Risk | €1,675,207 | 4 |
| Product_2 Total | €1,926,656 | 8 | |
| Product_20 | Promising | €34,384 | 2 |
| Product_20 Total | €34,384 | 2 | |
| Product_21 | Loyal | €2,100 | 1 |
| Product_21 | Promising | €19,000 | 1 |
| Product_21 | Need Attention | €37,900 | 1 |
| Product_21 Total | €59,000 | 3 | |
| Product_22 | At Risk | €74,438 | 1 |
| Product_22 Total | €74,438 | 1 | |
| Product_23 | At Risk | €25,202 | 1 |
| Product_23 | Promising | €168,000 | 1 |
| Product_23 | Need Attention | €262,000 | 1 |
| Product_23 Total | €455,202 | 3 | |
| Product_24 | Slipping | €6,068 | 2 |
| Product_24 | At Risk | €15,250 | 1 |
| Product_24 | Champions | €73,432 | 3 |
| Product_24 | Promising | €174,070 | 1 |
| Product_24 Total | €268,820 | 7 | |
| Product_25 | Slipping | €285,075 | 29 |
| Product_25 | Promising | €3,915,811 | 48 |
| Product_25 | At Risk | €5,134,326 | 38 |
| Product_25 | Need Attention | €6,316,483 | 55 |
| Product_25 | Loyal | €7,641,185 | 24 |
| Product_25 | Champions | €34,809,877 | 6 |
| Product_25 Total | €58,102,757 | 200 | |
| Product_26 | Need Attention | €21,235 | 51 |
| Product_26 | Slipping | €66,718 | 21 |
| Product_26 | Promising | €82,575 | 50 |
| Product_26 | At Risk | €151,881 | 48 |
| Product_26 | Loyal | €195,047 | 18 |
| Product_26 Total | €517,457 | 188 | |
| Product_27 | Slipping | €34,241 | 7 |
| Product_27 | Loyal | €41,305 | 3 |
| Product_27 | At Risk | €55,357 | 3 |
| Product_27 | Champions | €194,621 | 8 |
| Product_27 | Promising | €205,451 | 5 |
| Product_27 | Need Attention | €230,447 | 5 |
| Product_27 Total | €761,422 | 31 | |
| Product_28 | Slipping | €4,008 | 1 |
| Product_28 | Promising | €29,969 | 2 |
| Product_28 | Need Attention | €132,000 | 1 |
| Product_28 Total | €165,977 | 4 | |
| Product_29 | Promising | €600 | 2 |
| Product_29 | Slipping | €600 | 2 |
| Product_29 Total | €1,200 | 4 | |
| Product_3 | Slipping | €6,262 | 1 |
| Product_3 | Loyal | €112,408 | 4 |
| Product_3 | Champions | €218,856 | 3 |
| Product_3 Total | €337,525 | 8 | |
| Product_30 | At Risk | €33,260 | 1 |
| Product_30 Total | €33,260 | 1 | |
| Product_31 | At Risk | €1,556 | 3 |
| Product_31 | Slipping | €2,500 | 2 |
| Product_31 | Champions | €3,672 | 2 |
| Product_31 | Loyal | €5,936 | 4 |
| Product_31 | Promising | €9,726 | 5 |
| Product_31 | Need Attention | €10,850 | 5 |
| Product_31 Total | €34,240 | 21 | |
| Product_32 | Need Attention | €1,660 | 3 |
| Product_32 | Loyal | €6,011 | 3 |
| Product_32 | Promising | €16,784 | 6 |
| Product_32 | At Risk | €21,931 | 8 |
| Product_32 | Slipping | €72,232 | 13 |
| Product_32 | Champions | €179,880 | 1 |
| Product_32 Total | €298,497 | 34 | |
| Product_33 | Need Attention | €53,592 | 9 |
| Product_33 | Slipping | €60,917 | 16 |
| Product_33 | Promising | €84,572 | 11 |
| Product_33 | At Risk | €377,024 | 22 |
| Product_33 | Loyal | €1,033,291 | 9 |
| Product_33 | Champions | €2,180,028 | 8 |
| Product_33 Total | €3,789,423 | 75 | |
| Product_34 | Slipping | €5,217 | 4 |
| Product_34 | At Risk | €8,186 | 3 |
| Product_34 | Promising | €21,858 | 3 |
| Product_34 | Need Attention | €30,130 | 1 |
| Product_34 | Champions | €79,322 | 2 |
| Product_34 | Loyal | €108,701 | 2 |
| Product_34 Total | €253,414 | 15 | |
| Product_35 | Loyal | €6,208 | 2 |
| Product_35 | Promising | €6,358 | 3 |
| Product_35 | Champions | €12,480 | 2 |
| Product_35 | At Risk | €82,255 | 3 |
| Product_35 Total | €107,301 | 10 | |
| Product_36 | Promising | €2,852 | 1 |
| Product_36 | Slipping | €8,599 | 2 |
| Product_36 | At Risk | €14,000 | 1 |
| Product_36 | Champions | €61,160 | 1 |
| Product_36 Total | €86,611 | 5 | |
| Product_37 | At Risk | €7,066 | 2 |
| Product_37 | Champions | €19,992 | 1 |
| Product_37 | Promising | €21,995 | 9 |
| Product_37 | Slipping | €49,654 | 7 |
| Product_37 | Need Attention | €76,092 | 11 |
| Product_37 | Loyal | €111,646 | 7 |
| Product_37 Total | €286,444 | 37 | |
| Product_38 | At Risk | €5,866 | 1 |
| Product_38 | Champions | €12,224 | 1 |
| Product_38 | Loyal | €17,598 | 3 |
| Product_38 | Slipping | €17,598 | 3 |
| Product_38 | Promising | €30,786 | 1 |
| Product_38 Total | €84,071 | 9 | |
| Product_39 | Slipping | €1,075 | 3 |
| Product_39 | Promising | €1,300 | 3 |
| Product_39 | At Risk | €26,540 | 6 |
| Product_39 | Loyal | €26,810 | 3 |
| Product_39 | Champions | €380,216 | 6 |
| Product_39 Total | €435,941 | 21 | |
| Product_4 | Champions | €3,487,293 | 1 |
| Product_4 Total | €3,487,293 | 1 | |
| Product_40 | Loyal | €5,232 | 1 |
| Product_40 | Promising | €16,200 | 1 |
| Product_40 | Need Attention | €47,200 | 1 |
| Product_40 | At Risk | €65,500 | 2 |
| Product_40 | Champions | €325,232 | 2 |
| Product_40 Total | €459,364 | 7 | |
| Product_41 | Promising | €15,338 | 1 |
| Product_41 | Loyal | €25,450 | 3 |
| Product_41 | Slipping | €54,271 | 6 |
| Product_41 Total | €95,059 | 10 | |
| Product_42 | Loyal | €3,000 | 1 |
| Product_42 | Promising | €3,420 | 3 |
| Product_42 | Champions | €6,000 | 2 |
| Product_42 | Need Attention | €6,000 | 2 |
| Product_42 | At Risk | €345,251 | 2 |
| Product_42 Total | €363,671 | 10 | |
| Product_43 | Loyal | €26,180 | 2 |
| Product_43 | Need Attention | €104,658 | 2 |
| Product_43 | At Risk | €108,000 | 1 |
| Product_43 | Champions | €470,920 | 2 |
| Product_43 Total | €709,758 | 7 | |
| Product_44 | Champions | €24,615 | 1 |
| Product_44 | Slipping | €29,994 | 11 |
| Product_44 | Promising | €191,126 | 9 |
| Product_44 | Need Attention | €291,034 | 7 |
| Product_44 | At Risk | €413,186 | 10 |
| Product_44 | Loyal | €2,114,791 | 12 |
| Product_44 Total | €3,064,747 | 50 | |
| Product_45 | Champions | €546 | 1 |
| Product_45 | At Risk | €20,218 | 3 |
| Product_45 | Need Attention | €20,423 | 2 |
| Product_45 | Slipping | €21,655 | 3 |
| Product_45 | Promising | €59,317 | 3 |
| Product_45 Total | €122,159 | 12 | |
| Product_46 | Need Attention | €65,448 | 48 |
| Product_46 | Loyal | €102,464 | 14 |
| Product_46 | At Risk | €107,044 | 39 |
| Product_46 | Promising | €165,365 | 16 |
| Product_46 | Slipping | €214,720 | 26 |
| Product_46 Total | €655,042 | 143 | |
| Product_47 | Slipping | €15,351 | 12 |
| Product_47 | Champions | €21,079 | 2 |
| Product_47 | Need Attention | €29,218 | 9 |
| Product_47 | Loyal | €64,342 | 9 |
| Product_47 | At Risk | €95,905 | 12 |
| Product_47 | Promising | €123,773 | 20 |
| Product_47 Total | €349,668 | 64 | |
| Product_48 | Promising | €1,203 | 2 |
| Product_48 | Slipping | €30,100 | 4 |
| Product_48 | Need Attention | €246,376 | 3 |
| Product_48 Total | €277,679 | 9 | |
| Product_49 | Champions | €11,969 | 1 |
| Product_49 | Promising | €375,805 | 4 |
| Product_49 Total | €387,774 | 5 | |
| Product_5 | Slipping | €142,046 | 35 |
| Product_5 | Need Attention | €330,535 | 54 |
| Product_5 | Promising | €633,745 | 28 |
| Product_5 | Loyal | €4,422,451 | 36 |
| Product_5 | At Risk | €5,228,582 | 118 |
| Product_5 | Champions | €22,201,009 | 62 |
| Product_5 Total | €32,958,368 | 333 | |
| Product_50 | Loyal | €12,386 | 2 |
| Product_50 | Slipping | €47,951 | 10 |
| Product_50 | Promising | €113,170 | 10 |
| Product_50 | Need Attention | €205,885 | 3 |
| Product_50 | At Risk | €234,251 | 11 |
| Product_50 Total | €613,643 | 36 | |
| Product_51 | Promising | €12,441 | 3 |
| Product_51 | Slipping | €28,404 | 9 |
| Product_51 | Champions | €44,814 | 3 |
| Product_51 | Loyal | €150,201 | 7 |
| Product_51 | Need Attention | €245,074 | 10 |
| Product_51 | At Risk | €927,823 | 5 |
| Product_51 Total | €1,408,757 | 37 | |
| Product_52 | Slipping | €19,976 | 1 |
| Product_52 | Need Attention | €41,863 | 1 |
| Product_52 | Promising | €48,025 | 1 |
| Product_52 | At Risk | €197,895 | 2 |
| Product_52 Total | €307,758 | 5 | |
| Product_53 | At Risk | €2,352 | 4 |
| Product_53 | Need Attention | €2,641 | 4 |
| Product_53 | Loyal | €6,600 | 3 |
| Product_53 | Promising | €8,176 | 8 |
| Product_53 | Slipping | €14,140 | 2 |
| Product_53 Total | €33,908 | 21 | |
| Product_54 | Slipping | €5,600 | 2 |
| Product_54 | Promising | €6,800 | 2 |
| Product_54 Total | €12,400 | 4 | |
| Product_55 | Slipping | €5,800 | 1 |
| Product_55 | Loyal | €25,110 | 1 |
| Product_55 | At Risk | €97,500 | 1 |
| Product_55 | Champions | €227,000 | 1 |
| Product_55 | Promising | €413,100 | 3 |
| Product_55 Total | €768,510 | 7 | |
| Product_56 | Promising | €21,654 | 3 |
| Product_56 | At Risk | €306,800 | 1 |
| Product_56 Total | €328,454 | 4 | |
| Product_57 | Loyal | €54,157 | 1 |
| Product_57 | Need Attention | €54,157 | 1 |
| Product_57 | Promising | €99,577 | 3 |
| Product_57 | Champions | €108,314 | 2 |
| Product_57 Total | €316,205 | 7 | |
| Product_58 | Loyal | €3,170 | 1 |
| Product_58 | Promising | €11,000 | 1 |
| Product_58 | Slipping | €24,728 | 2 |
| Product_58 | At Risk | €33,369 | 2 |
| Product_58 Total | €72,267 | 6 | |
| Product_59 | At Risk | €557,600 | 1 |
| Product_59 Total | €557,600 | 1 | |
| Product_6 | Slipping | €85,173 | 23 |
| Product_6 | Promising | €532,466 | 24 |
| Product_6 | Need Attention | €574,809 | 47 |
| Product_6 | Loyal | €842,729 | 29 |
| Product_6 | Champions | €1,388,716 | 20 |
| Product_6 | At Risk | €1,908,955 | 67 |
| Product_6 Total | €5,332,848 | 210 | |
| Product_60 | Need Attention | €1,444 | 1 |
| Product_60 | Loyal | €3,833 | 3 |
| Product_60 Total | €5,277 | 4 | |
| Product_61 | Promising | €1,100 | 1 |
| Product_61 Total | €1,100 | 1 | |
| Product_62 | Promising | €27,460 | 1 |
| Product_62 Total | €27,460 | 1 | |
| Product_7 | Loyal | €39,963 | 6 |
| Product_7 | Need Attention | €62,016 | 7 |
| Product_7 | Slipping | €80,332 | 8 |
| Product_7 | Promising | €110,574 | 9 |
| Product_7 | Champions | €1,700,079 | 1 |
| Product_7 | At Risk | €1,747,961 | 6 |
| Product_7 Total | €3,740,925 | 37 | |
| Product_8 | Slipping | €426,570 | 56 |
| Product_8 | Loyal | €1,765,114 | 27 |
| Product_8 | Need Attention | €4,456,600 | 37 |
| Product_8 | Champions | €5,211,674 | 7 |
| Product_8 | Promising | €5,967,544 | 59 |
| Product_8 | At Risk | €13,838,037 | 52 |
| Product_8 Total | €31,665,539 | 238 | |
| Product_9 | Need Attention | €1,789 | 4 |
| Product_9 | Promising | €5,557 | 3 |
| Product_9 | Loyal | €89,084 | 1 |
| Product_9 | At Risk | €157,747 | 5 |
| Product_9 Total | €254,178 | 13 |
segtoview="At Risk"
la<-revprod%>%filter(Segment==segtoview)
tree2<-revprod%>%filter(Segment==segtoview)%>%ggplot(aes(area =Revenue, fill =Revenue, label = paste(Product.Name,format(as.numeric(Revenue/1000000),digits=0,nsmall=2, big.mark=","),"M€")))+geom_treemap() +geom_treemap_text(fontface = "italic", colour = "white", place = "centre",grow = T,reflow=T)+theme(legend.position = "right")+labs(title =paste("Revenue per Product" ,segtoview, "Segment\n",segtoview, "Revenue=",format(sum(la$Revenue),big.mark = ","),"€"),caption = "The area of each tile represents the proportion of Revenue")+scale_fill_continuous(low="orangered1",high="darkblue",label = dollar_format(suffix = "€", prefix = ""))
tree2
revseg2017%>%ggplot(aes(Segment,Revenue))+geom_bar(stat="identity",fill="steel blue")+geom_text(aes(label=Revenue),size=3.5)+scale_y_continuous(breaks=seq(0,90000000,15000000),labels = dollar_format(suffix = "€", prefix = ""))+theme_minimal()+coord_flip()
#+scale_x_discrete(limits=rev(myorder))+coord_flip()